IF exists(SELECT * FROM dbo.sysobjects WHERE name='WEB_schoolverloop' AND xtype='P') 
BEGIN
	DROP PROCEDURE WEB_schoolverloop
END
GO

CREATE   procedure WEB_schoolverloop
(@iNr char(7))
as
set nocount on
create table #hulptabel(hoofdNr int,volgNr bigint,tekst varchar(8000))
insert into #hulptabel
select 1 as hoofdNr,0 as volgNr,'<?xml version="1.0" encoding="ISO-8859-1" ?>'
union
select 2,0,'<leerling><naam>'+ isNull(ln.voornaam,'') + ' ' + isNull(ln.achternaam,'') + '</naam>'
  from leerlingen..leerlingen as ln 
    where ln.inschrijvingsNr = @iNr
union
select 3,1,
  case when len(lagereSchoolnummer)>0 then
     '<lagereSchool><naam>' + isNull(ls.school,'') + '</naam>' +
           '<straat>' + isNull(ls.straat,'') + ' ' + isNull(ls.nummer,'') +  '</straat>' +
           '<plaats>' + isNull(ls.postcode,'') + ' ' + isNull(ls.gemeente,'') + '</plaats>' +
           '<klas>' + isNull(l.lagereSchoolKlas,'') + '</klas>' +
           '<resultaat>' + isNull(l.lagereSchoolResultaat,'') + '</resultaat>' +
           '</lagereSchool>'
	else '' end
from lagereSchool as l
	left join lijstLagereScholen as ls
		on l.lagereSchoolNummer = ls.schoolNr
where l.inschrijvingsNr = @iNr
union
select 4,1,
  case when len(andereSchoolnummer)>0 then
     '<andereSchool><naam>' + isNull(ls.school,'') + '</naam>' +
        '<straat>' + isNull(ls.straat,'') + ' ' + isNull(ls.nummer,'') +  '</straat>' +
        '<plaats>' + isNull(ls.postcode,'') + ' ' + isNull(ls.gemeente,'') + '</plaats>' +
        '<klas>' + isNull(l.andereSchoolKlas,'') + '</klas>' +
        '<resultaat>' + isNull(l.andereSchoolResultaat,'') + '</resultaat>' +
 	'<richting>' + isNull(andereSchoolRichting,'') + '</richting>' +
        '<attest>' + isNull(andereSchoolAttest,'') + '</attest>' +
        '<advies>' + isNull(andereSchoolAdvies,'') + '</advies>' +
        '<opmerkingen>' + isNull(andereSchoolOpmerkingen,'') + '</opmerkingen>' +
        '</andereSchool>'
	else '' end
from andereSchool as l
	left join lijstAndereScholen as ls
		on l.andereSchoolNummer = ls.schoolNr
where l.inschrijvingsNr = @iNr

insert into #hulptabel
  execute WEB_schoolverloopBelangstellingstest 6,@iNr

insert into #hulptabel
  execute WEB_schoolverloopHistoriek 10,@iNr

insert into #hulptabel
  execute WEB_schoolverloopKeuze2 11,@iNr  

insert into #hulptabel
  execute WEB_schoolverloopKeuze4 12,@iNr

insert into #hulptabel  
  select 99,1,'</leerling>'

select * from #hulptabel
order by hoofdNr,volgNr